﻿CREATE PROCEDURE [dbo].[proc_Employee_Wage_Create]
(
	@startindex int,
	@endindex int,
	@DepartmentId int,
	@EName nvarchar(50),
	@CreateName nvarchar(50)
)
as
	Begin
	Declare @CompanyId int,@FaFangMonth nvarchar
	Set @CompanyId = (Select CompanyId From aspnet_users Where UserName=@CreateName) 
	IF (Select Count(Id) From Employee_Wage Where FaFangMonth = (select convert(varchar(7),dateadd(mm,-1,getdate()),120)) and CompanyId = @CompanyId) = 0
	Begin
		Insert Into Employee_Wage(UserName,EName,CompanyId,DepartmentId,ZhiWu,GradeId,PostCategoryId,PostId,BasicWage,PostWage,WorkWage,JxWage,Allowance,FaFangMonth,CreateName,CreateDate,TiCheng,MTiCheng,YjBonus)
		Select 
			e.UserName,
			e.EName,
			e.CompanyId,
			e.DepartmentId,
			
			e.ZhiWu,
			e.GradeId,
			e.PostCategory,
			e.PostId,
			(isnull((select Wage from Grade where id=E.GradeId),0)) as BasicWage,
			(isnull((select PostWage from Post where id=E.PostId),0)) as PostWage,
			(isnull((datediff(dd,ruzhidate,getdate())/360*100),0)) as WorkWage,
			--(isnull((select JxWage from Post where id=E.PostId),0)) as JxWage,
			IsNUll((select JixiaoPrice from employee_wagejx where CompanyId = @CompanyId and Sales=e.UserName and Convert(varchar(8),FaFangDate,120) = (select convert(varchar(7),dateadd(mm,-1,getdate()),120)) ),0) as JxWage,
			(isnull((select amount from Employee_Allowance EA where id=(select Allowance from post p where p.id=E.PostId)),0)) as Allowance,
			(select convert(varchar(7),dateadd(mm,-1,getdate()),120) as FaFangMonth),
			@CreateName,
			getdate(),
			IsNUll((select tichengprice from employee_wagejx where CompanyId = @CompanyId and Sales=e.UserName and Convert(varchar(8),FaFangDate,120) = (select convert(varchar(7),dateadd(mm,-1,getdate()),120))),0) as TiCheng,
			IsNUll((select mtichengprice from employee_wagejx where CompanyId = @CompanyId and Sales=e.UserName and Convert(varchar(8),FaFangDate,120) = (select convert(varchar(7),dateadd(mm,-1,getdate()),120))),0) as MTiCheng,
			IsNUll((select JiangjinPrice from employee_wagejx where CompanyId = @CompanyId and Sales=e.UserName and Convert(varchar(8),FaFangDate,120) = (select convert(varchar(7),dateadd(mm,-1,getdate()),120))),0) as YjBonus
		From
			Employee e
		Where
			e.CompanyId = @CompanyId
			and e.State <> 5
			and e.IsTxlz = 0
			and ((e.departmentid is not null) or( e.departmentid = 0))
		End
	Else
		Begin
			declare @indextable table(id int identity(1,1),nid int)
			;WITH ChildDepartment(ID,DName,PId) AS
			(
				SELECT ID,DName,ParentID FROM department WHERE id=@departmentid
			    
				UNION ALL
				SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
				INNER JOIN department ON d.ID = department.ParentID
			)
			
			insert into @indextable(nid) select Id from ChildDepartment;
			
			;WITH list As(Select ROW_NUMBER() OVER (ORDER BY Employee_Wage.Id DESC)AS Row,
				Id,
				UserName,
				EName,
				CompanyId,
				(select CompanyName from Company where id=Employee_Wage.CompanyId) as CompanyName,
				(isnull(Employee_Wage.DepartmentId,0)) as DepartmentId,
				(isnull((select DName from Department where id=Employee_Wage.DepartmentId),'''')) as DepartmentName,
				isnull(ZhiWu,'') ZhiWu,
				GradeId,
				(isnull((select Title from Grade where id=Employee_Wage.GradeId),'''')) as GradeName,
				PostCategoryId,
				(isnull((select title from post_category where id=Employee_Wage.PostCategoryId),'''')) as PostCategoryName,
				PostId,
				(isnull((select PostName from Post where id=Employee_Wage.PostId),'''')) as PostName,
				BasicWage,
				PostWage,
				WorkWage,
				JxWage,
				ManQin,	
				Allowance,
				TiCheng,
				MTiCheng,
				YjBonus,
				LsBonus,
				bxKouKuan,
				YjKouKuan,
				ChuQinKouKuan,
				ChiDaoKouKuan,
				OtherKouKuan,
				FaFangMonth,
				CreateName,
				CreateDate
			From
				Employee_Wage
				Where
					(@CompanyId=0 or CompanyId = @CompanyId)
					and FaFangMonth = (select convert(varchar(7),dateadd(mm,-1,getdate()),120) ) 
					and (@Departmentid=0 or Departmentid in(select c.nid from @indextable c))
					and EName like '%'+@EName+'%'
			)
			
			Select * From list 
		End
	End
